> | ## Charlie Calvert's C++ Builder Unleashed ## - 8 - ## Database Basics and Database Tools This chapter provides an introduction to BCB database programming. I start by showing how to create aliases and simple database applications, and then move on to a discussion of various conventions that I use when programming databases. Next, I discuss key database tools that ship with BCB, and close with a discussion of getting ODBC and TCP/IP set up on your system. In subsequent chapters I move on to a discussion of advanced client/server issues. I am covering such basic material as you find in this chapter in a book on intermediate-to-advanced programming because there are so many C++ programmers who are new to the database world. If you have much experience in C++ desktop and systems programming, but little database experience, you should take the time to work through this chapter. BCB makes this subject easy to master, and once you have database tools at your fingertips, you will be surprised how many uses you can find for them. This is especially true in the data-centric, Internet-based world most programmers live in these days. More advanced database programmers should also read this chapter, because I include an overview of the BCB database architecture. All serious developers need to be concerned with matters covered in this chapter such as Object Repositories, business rules, the proper use of `TDataModule`, and so on. More specifically, the subjects covered in this chapter include introductions to the following: The `TTable`, `TQuery`, and `TDataSource` objects. The discussion of these subjects will show you how to connect to tables with and without SQL. The tools available for creating aliases. The Object Repository, Database Explorer, and SQL Monitor. The `TDataModule` keeps your form clear of database objects, and it consolidates your database code and rules in a single object that can be shared in multiple applications. In particular, it enables you to place nonvisual components, and particularly nonvisual database components, on a special window that remains hidden at runtime. This window can be stored in the Object Repository and can be used to specify business rules that can be reused by a wide range of projects and programmers. The last part of this chapter covers the different means for accessing data from inside BCB. For instance, you can use the BDE, ODBC, and SQL Links. In this section you learn about the difference between Paradox, dBASE, InterBase, Oracle, and Sybase, as well as other related matters. The chapter ends with a discussion of using ODBC, and of setting up TCP/IP on your system. After you have covered the basics in this chapter, the next step is to move onto the in-depth discussions of these topics found in the next few chapters. In particular, the next chapter covers the `TTable` object in depth, and the following chapter covers the SQL-based `TQuery` object. Here is an overview of what lies ahead: Chapter 8: This chapter, which covers database basics, including introductions to `TTable` and `TQuery` Chapter 9: An in-depth look at `TTable` and `TDataSet` Chapter 10: An in-depth look at`TQuery` and an introduction to SQL programming Subsequent chapters in this section of the book cover intermediate and advanced database topics. For instance, you will find more material on the database tools in general and on CASE tools in particular, in Chapters 17 and 18. The logic behind arranging things this way is to enable you to first get an introduction to BCB and databases, and to then dig into the subject once you understand the issues involved. This way I can keep the simplest subject matter out of the latter chapters so they can focus on relatively serious programming issues. If you are interested primarily in design issues, you can safely skim through this chapter, looking only for the parts that interest you. If you have not built a BCB database program before, you should read this entire chapter through from beginning to end. ### On Setting Up the Database Tools BCB comes ready to run Paradox, dBASE, and local InterBase tables. If you have the client/server version of the product, it comes with the full InterBase server. The client/server product also lets you access any DB2, Informix, MS SQL Server, Sybase, and Oracle you have available. The very first version of BCB does not ship with built-in support for Access or FoxPro, but if you have Delphi 3.0 installed on your system, you can get access to these features automatically. NOTE: The issue here is that BCB 1.0 ships with an older version of the BDE than Delphi 3.0, because the new version of the BDE was not available at BCB ship time. If you can get the most recent version of the BDE, BCB will work with it automatically, which means you can get at Access tables. You can sometimes download the BDE directly from the Borland Web site at `www.borland.com`. If you can't get the most recent version of the BDE, you can use ODBC to, as they say, access "Access." It does not matter that BCB shipped with an earlier version of the BDE than the one you may be running on your system. The BDE was designed to handle this type of situation, and it is entirely regular. I use the most recent version of the BDE with my copy of BCB, and I never have any hint of trouble. The primary way to access data from BCB involves the BDE, or Borland Database Engine. This engine, which is explained in more depth near the end of this chapter, is the backbone of the BCB database tools. Paradox, dBASE, and Access tables can be reached directly from the BDE. To access the other databases, you also need SQL Links, which is a product that ships with the client/server version of Delphi. One simple way to find out what database access you have from your system involves dropping down a `TDatabase` object on a form and examining its `DriverName` property in the Object Inspector. This property contains a list of the installed drivers on your system. For instance, all the databases listed earlier in this section appear in this Property Editor on my system. (Paradox and dBASE tables are accessed through the driver labeled "Standard.") If you are having trouble accessing some or all databases from your system, the best remedy is usually to do a complete uninstall and a complete reinstall. The local database systems ought to work automatically out of the box without any effort on your part. For instance, I have probably installed BCB 50 times on a variety of systems, and I have always been able to reach Paradox or dBASE tables immediately after installation. Users of the BDE should be aware that BCB ships with a tool called the BDE Configuration utility. This application can be accessed from the Start menu. You can use this tool to make sure the BDE itself is running correctly. As a rule, if the BDE is not running, you will not be able to access databases from inside of BCB. ### Database Basics To create a simple database application, start by placing a `TTable`, a `TDataSource`, and a `TDBGrid` component on a form, as shown in Figure 8.1. FIGURE 8.1. `TTable`, `TDataSource`, and `TDBGrid` arranged on a form. Wire these three controls together by completing the following simple steps: 1. Connect the `DataSource` property of the `TDBGrid` to `DataSource1`. 2. Connect the `DataSet` property of the `TDataSource` control to `Table1`. After completion of these steps, the three components are hooked together and can communicate with one another. Connecting the `TTable` object to a table that resides on disk is a three-step process: 1. Set the `DatabaseName` property either to a valid alias or, in the case of Paradox or dBASE, to the subdirectory where your data resides. For the example currently under discussion, you can set the `DatabaseName` property to the `BCDEMOS` alias, which is created by default during BCB's installation. Alternatively, you could type `c:\CBuilder\demos\data` into the DatabaseName Property Editor, where you might need to change some aspects of this path depending on where you choose to install BCB. 2. Set the `TableName` property to the name of the table you want to view; for instance, you might choose the `CUSTOMER.DB` table. The Property Editor drops down a list of available tables, so there is no need for you to type anything; the whole job can be done with the mouse. 3. Set the `Active` property, found at the very top of the Object Inspector, to `true`. When you are done, the Object Inspector should look as it does in Figure 8.2. FIGURE 8.2. The Object Inspector after connecting to a table called `Customer`, using an alias called `BCDEMOS`. If you have completed all these steps properly, you should now be looking at the data from the table you chose, as shown in Figure 8.3. To take this process one step further, you can compile and run the program and then begin browsing and editing your data. FIGURE 8.3. Simple form displaying the contents of `CUSTOMER.DB`. If you want to simplify the task of browsing through the data in your application, you can go back into design mode and add the `TDBNavigator` control to the program. To hook this control into the loop, all you need to do is set its `DataSource` property to `DataSource1`. Now you can run the program and begin iterating through the records with the navigator, as shown in Figure 8.4. In Figure 8.4, most of the functionality of the `TDBNavigator` has been turned off by manipulating its `VisibleButtons` property. For instance, a navigator can automatically enable you to edit, insert, delete, post, cancel, and refresh. All those capabilities have been disabled and hidden in the form shown here by manipulating the `VisibleButtons` property of `TDBNavigator`. FIGURE 8.4. A simple database program with a `TDBNavigator` control. A program like the one described here is found on the CD-ROM that accompanies this book as `Simple.cpp`. Programs like this are the bread and butter of the database world. You might even find it useful to create several programs of this type, just be to be sure you can do it quickly and easily with little thought. BCB database programmers work with the `TTable`, `TDataSource`, and `TDBGrid` objects on a regular basis, and the basics of their operation should be as easy as tying your shoe. ### Naming and Architectural Conventions In this section I lay out a number of conventions that I generally abide by in programming projects. Before beginning, I should emphasize that these are merely conventions. There are no hard and fast rules in this area, and you should feel free to follow my suggestions only to the degree that they suit your taste. In fact, you will find that I myself do not follow these conventions one hundred percent of the time, though I generally conform to them when I am not feeling too rushed. I also have included legacy code in this book in which I did not adopt the techniques that I currently believe are best. In some cases, I have updated the legacy code, but some sample programs still use old conventions. When arranging tables on a data module, I usually follow some simple naming conventions. If I attach a `TTable` object to a table called `Customer`, I will call the `TTable` object `CustomerTable`. The data source attached to that table will generally be called `CustomerSource`. NOTE: An alternative technique, called Hungarian notation, would name all `TTable` objects `tbl`XXX, where the XXX is the name of the table you want to use: `tblCustomer`, `tblBioLife`, and so on. You could then prefix `ds` before the table name to designate the name of the data source: `dsCustomer`, `dsBioLife`, and so on. This was the technique I used in the past, but which I no longer believe to be best. The Hungarian system enables you to automatically group all the objects shown in the Object Inspector according to type. If all the tables begin with `tbl`, they will appear together in the Object Inspector. Despite this advantage, I have decided against Hungarian notation on the grounds that it tends to make even simple code appear a bit abstruse. Recondite code has a certain emotional appeal, but it is not the effect I want to strive for in a book that champions the virtues of clear, easy-to-read logic. In general, I try to avoid systems that force me to use hard to read abbreviations, or that clutter up the beginnings of words. In particular, I find an identifier like `dsCustomer` unpleasant because it makes the type of the variable appear more important than the name of the variable. This system also makes it hard to read the identifier, because you have to mentally skip over the first syllable before getting to the key piece of information contained in the name. Of course, my least favorite naming convention abbreviates both the type and the variable name:
>     DDSURFACEDESC       ddsd;

>     HBITMAP             hbm;

>     RGBQUAD *           prgb;
> Incredibly, these samples are taken from source code distributed by a major software company in order to promote a new API. I am at least tempted to believe that the people who came up with these variable names were trying to be funny, or perhaps just to pull someone's leg. At any rate, these are classic examples of naming conventions that I try to avoid. So, in this book, it will generally be `CustomerTable`, and not `tblCustomer`. If you prefer some other system, you should feel free to pursue your tastes. After all, many of the best programmers use Hungarian notation on a regular basis. In fact, in the world of C++, my tastes probably represent a minority opinion. In simple projects that have only one data module, I will usually call the files associated with the data module `DMod1.cpp` and `DMod1.h`. The `TDataModule` object itself I usually rename to `TDMod`. In more complex projects that have multiple data modules I might rename the data module to something more meaningful such as `TDModAddress`, and I might then save the file under the name `DModAddress1.cpp`. Please note that my convention is to name the file in which a data module or form is stored as the same name as the data module or form, except that I append a `1` to it. Thus the file in which a data module called `TDMod` is stored will be called `DMod1`. This prevents name conflicts between the object name and the filename. If I have a form called `TAddress`, I will save it in a file called `Address1.cpp`. The one exception to the previous rule is that I tend to name the main module of a project `Main.cpp`, and I then usually keep the main form default name of `Form1`. Please understand that I have included this section more as a courtesy than out of a desire to attempt to force my tastes on someone else. I want you to know and understand the conventions I use, but you should feel free to use the techniques that you feel work best. Enough on naming conventions. It's time now to move on to a related, but slightly different matter regarding the proper use of data modules. ### Using the TQuery Object You can create a BCB SQL statement by using a `TQuery` component in the following manner: 1. Drop down `TQuery`, `TDataSource`, and `TDBGrid` objects on a form and wire them together. 2. Assign an alias to the `DatabaseName` property of the `TQuery` object. For instance, use the `BCDEMOS` or `DBDEMOS` alias. These aliases are created automatically by BCB and Delphi, respectively, during installation. 3. Use the `SQL` property to enter a SQL statement such as `Select * from Country`. 4. Set the `Active` property to `True`. If you completed each step correctly, and if the BDE is set up correctly, the grid should now contain the records from the `Country` table. If you're working with local data, you can substitute a fully qualified subdirectory path for an alias. When using the latter method, it's best if you don't include the actual name of a table, but only the subdirectory in which one or more tables exist. In my opinion, however, it is almost always better to work with an alias rather than specify the path directly in the `DatabaseName` property. That's all I'm going to say about `TQuery` for now. Later in this chapter I discuss the SQL monitor tool that comes with BCB. In subsequent chapters I begin using SQL statements more heavily. I find it easier to use `TTable` than `TQuery` for many basic database operations. However, as I discuss matters of increasing complexity throughout the database section of this book, I will rely more and more on SQL. #### The Data Module Earlier in this chapter, you placed a `TTable` and `TDataSource` component on the same form with your visual components. When you ran the program, the icons representing these components disappeared. However, they are visible at design time and have a tendency to clutter up the form. Partially out of a desire to eliminate this clutter, BCB features a component called a `TDataModule`, which can be used to store nonvisual controls such as `TTable` and `TDataSource`. A program on disk called `SimpleTable` shows how to use the `TDataModule` component. To get started working with `TDataModules`, first begin a new application. Next, choose File | New and select the Data Module component from the New page of the New Items dialog, as shown in Figure 8.5. You can also choose to create a data module directly from the New Data Module option on the File menu. You should, however, get used to using the Object Repository as it plays a big role in BCB programming. FIGURE 8.5. Selecting the `TDataModule` component from the New Items dialog. NOTE: A `TDataModule` is not the same thing as a form. For instance, if you look in its ancestry you will see that it is a direct descendant of `TComponent`. When you first see a `TDataModule` object, there is a tendency to view it as merely a special kind of form, which is, to some degree, true, at least in a very nontechnical sense. However, the hierarchy for a `TForm` component looks like this:
>     -TComponent

>       -TControl

>         -TWinControl

>           -TScrollingWinControl

>             -TForm
> The hierarchy for a `TDataModule`, on the other hand, looks like this:
>     -TComponent

>       -TDataModule
Clearly, `TForms` and `TDataModules` are two very different beasts, despite some apparent similarities between them. The header file that contains the declaration for the `TDataModule` class is `FORMS.HPP`. If you have the Pascal source to the VCL, the `TDataModule` object is found in `FORMS.PAS`. After adding a `TDataModule` object to your application, take a moment to save your code. You might save `Unit1` as `MAIN.CPP` and `Unit2` as `DMOD1.CPP`. Click `Form1`, open the File menu, and choose the Include Unit Header expert from the menu. In the Include Unit dialog, select `DMod1` and press OK. This is a simple way of automatically inserting an `#include` directive at the top of `Unit1`. In particular, the following changes are made to your code:
    #include <vcl\vcl.h>

    #pragma hdrstop

    #include "Main.h"

    #include "DMod1.h" // This directive references the data module

    #pragma resource "*.dfm"
> You can, of course, type in the `#include` directive without using the small expert found on the File menu. There is no particular advantage in using the expert other than its ease of use. Remember, however, that if you want to include a unit in your project, it is generally not enough to simply add a header file to a unit. You must also be sure the unit has been explicitly added to your project. In the case discussed here, there is no need to explicitly add `Unit2` (a.k.a. `DMod1`) to the project, because it was done automatically when you first created the unit. NOTE: This is a time when some programmers may need to force themselves to abandon the old "command-line" attitude, and to instead embrace a visual tool that can help make you more productive. As always, the choice is yours, but if the visual tools are easier to use, and if they make you more productive, you should consider using them. Command-line programming has an honorable place in this world, but it is generally not part of the attitude that makes for good RAD programmers. Let me tack on one additional piece of information to this note. If you want to add a module to a project without using the project manager, and without editing a makefile, you can use the following syntax:
>     #pragma link "dmod1.obj"
This syntax can be very useful when adding components to the component palette. See the `FTP2.cpp` module in the `Utils` directory from the CD-ROM that ships with this book for an example of using this approach. Arrange `Form1` and `DataModule2` on the screen so you can view them both at the same time. Drop a `TTable` and `TDataSource` component on the data module, as shown in Figure 8.6. FIGURE 8.6. `Form1` and `DataModule2` arranged on the screen so that you can easily view them both at the same time. NOTE: For various reasons I snapped my screen shots for this book at 640x480 resolution. Needless to say, I don't usually run BCB at that resolution. 1024x768 is probably a more reasonable size when working with an environment like this, though even higher resolutions would be better. 800x600 is tolerable, but I still feel the pinch when working at that low a resolution. Wire the `TDataSource` to the `TTable` object, and set the `DatabaseName` of the `TTable` object to `BCDEMOS` and the `TableName` to `BioLife`. Set the `Active` property of the `TTable` object to `True`. Right-click the `TTable` object and bring up the Fields Editor. Right-click the Fields Editor, and bring up the AddFields dialog. Make sure all the fields in the dialog are selected, which is the default behavior for the tool. Click the OK button. The Fields Editor now contains a list of all the fields in the `BioLife` table. To add these fields to your form, simply click one or more fields, hold the left mouse button down, and drag the fields onto the form. For instance, select the Graphics field, making sure that it is the only one highlighted. Now drag it onto the form. When you let go of the left mouse button, the Graphics field will automatically display itself in a `TDBImage` component. Drag over several other fields, and create a form that looks something like the image in Figure 8.7. FIGURE 8.7. Some of the fields of the `BioLife` table arranged in visual controls on a `TForm` object. If you want to drag multiple fields over at the same time, perform a multiselect operation in the Fields Editor, just as you would in a list box. Now drag all the fields over to the main form at once. Most likely they will scroll on past the bottom of your form when you insert them, but you can fix this easily enough by aligning them as you like with the mouse. The `TDBImage` component, where the picture of the fish is displayed, may be in some disarray when you first insert the components on the form. To straighten things out, select the `TDBImage` component, go to the Object Inspector, and set the `Stretch` property to `True`. A sample program illustrating these principles ships on the CD-ROM that accompanies this book. It is called SimpleDataModule. ### The Purpose of TDataModule Now that you know how to use the `TDataModule`, let me add a few words on its significance. This component's primary purpose is to provide a place where you can define the means for accessing a set of tables. However, it is also a place to put business rules, and a place to create reusable means of accessing data. Client/server database programmers often want to put all the rules for accessing data on the server side. Indeed, BCB supports this paradigm, and you can use stored procedures, views, and other advanced database technologies to whatever degree you want when accessing SQL databases. However, you also have the ability to define a set of rules that live on the client side, inside a `TDataModule`. You can then use the Object Repository from the File | New menu choice to store this form in a place where it can be reused by multiple programmers. To put a form in the Object Repository, right-click it and chose Add to Repository from the menu. I discuss the Object Repository in more depth later in this chapter. There is no simple way to decide when it is best to put rules on the server side or when it is best to put them inside a `TDataModule`. Often the best solution is to use a combination of the two techniques. Put things on the server side when that is simplest, and store things on the client side when you think the power of C++ and its strong debuggers will be useful to you. There is considerable complexity in the whole set of related subjects that involve designing databases, creating business rules, and creating metadata such as stored procedures on a server. Many of these topics will be explored in considerable depth in the next few chapters. However, a thorough examination of these topics requires considerably more scope than I have in this book. ### Conventions Regarding the Use of TDataModules When working with database code, I often prefer to put my `TTable`, `TQuery`, and `TDatasource` objects in a `TDataModule`, rather than placing them on a form. In other words, I think there are architectual reasons for using `TDataModules` rather than placing tables directly on a form. There are several interrelated advantages to this scheme, most of which have to do with proper design issues. In particular, the scheme outlined previously enables me to do the following: Separate my database code and my GUI code. In an ideal situation, all the front-end code is in my form, and all the database code is in the `TDataModule`. Protect the data in database code. It can sometimes be bad practice to directly access any of the methods or fields of an object from inside another object. Instead, you can present a series of public properties and methods as the interface for an object. There is, however, nothing wrong with accessing the properties of a `TTable` and `TQuery` object through pointer notation. In other words, the rules of aggregation state that it is acceptable and safe to write `DMod->Table1`, rather than trying to hide `Table1` behind methods of `TDMod` that wrap the object and hide it from sight. Promote reuse. By isolating the database code in the `TDataModule`, and by protecting the data associated with my database code, I am creating a robust object that can be reused in multiple programs. In particular, several programs may want to access data in the same manner. By using a `TDataModule` correctly, you can encapsulate your data, and its associated rules, inside a single related object that can be reused in multiple programs. This is one way to create a set of reusable business rules that can be shared by multiple members of your team, or by multiple programmers. I will often use the constructor of a `TDataModule` or its `OnCreate` event to open up the tables or data modules used in a project:
    void __fastcall TDModBioLife::DModBioLifeCreate(TObject *Sender)

    {

      BioLifeTable->Open();

    }
This is the proper and ideal way to do things. I want to stress, however, that it is also correct from an OOP standpoint to access the same table from inside your main form through the `scoping` operator:
    void __fastcall TForm1::Button1OnClick(Tobject *Sender)

    {

      DMod->BioLifeTable->Open();

    }
You can use either technique, depending on your needs. Most of the time, you will use the second technique shown here, the one that uses `scoping` operators. There are, however, some good arguments in favor of using the first method. In particular, the first technique is pure from an OOP point of view in that it completely hides the details of what goes on in the `TDMod` object. One problem with the rigorous technique illustrated by the first example is that it can add complexity to simple programs. If you only need to access one table, and only need one `TDataSource` object, even the simple act of creating a data module can seem like overkill. Going even further and insisting that the code for manipulating the table also reside in the `TDataModule` can then seem almost absurdly roundabout and abstruse. In the type of simple database projects that you will see in this chapter, it is possible to forgo the use of data modules altogether and to instead place the `TTable` and `TDataSource` objects directly on your main form. However, I will generally use a `TDataModule` even in such simple cases simply because it is the best way to architect an application. The point is to get in the habit of doing things the right way, because ultimately, in large scale projects, decisions such as this do matter. NOTE: Data modules can be used not only for data controls, but for all nonvisual controls. You can, for instance, place a `TMenu` object on a data module, and then add the data module's header file to your main form, thereby accessing the `TMenu` object through the Object Inspector. The problem with this technique, of course, is that the methods you want to access from the menu are not always going to be located in the data module. Another issue is that your form and the data module will then be bound together, at least to some degree. Remember that one of the key features of data modules is that they provide a place to store a set of business rules. You can create tables and queries, link them together, and use code to define rules regarding the way they work. To replicate these rules in multiple projects, simply reuse the data module that contains them. ### The Object Repository In the last section, I said that data modules can help promote reuse of code. BCB has a specific mechanism called an Object Repository that can help with this process. In particular, Object Repositories are a place where you can store data modules and forms so that they can be reused in multiple applications. If you define a set of business rules in a data module, you can save it to the Object Repository and reuse it in multiple projects. This helps you propagate the rules and promote conformity to them across a wide range of projects. The simplest way to introduce you to the Object Repository is to just lead you step-by-step through the process of using it. After you have seen how it works, I will take a moment to explain its significance. Save the program you created in the last section as follows: 1. Save `Unit1` as `Main.cpp`. 2. Save `Unit2` as `DModBiolife.cpp`. 3. Save the project file as `Biolifeapp`. 4. Rename the table and data source to `BioLifeTable` and `BioLifeSource`. 5. Select the data module and use the Object Inspector to rename it from `DataModule2` to `DModBioLife`. Right-click the data module and select Add To Repository. Fill in the Add To Repository dialog by setting the Title, Description, and Author fields as you see fit. In the Page drop-down combo, select Data modules. Use the Browse button to select an icon from the `..BCB\images\icon` subdirectory, or from any place else where you might have some icons stored. Start a new project. Choose File | New. This time, instead of choosing the Data module component from the New page, select the Data modules page and choose the `DModBioLife` component that you just finished creating. When it appears on the screen, you will see that it contains a `TTable` and `TDataSource` component. The components are wired together, and the `TTable` object is set to the `BioLife` table with its `Active` property set to `True`. To access this table from `Form1`, you must first employ the Include Unit Header menu option from the File menu to add `Unit2` to the uses clause in `Unit1`. Go to the DataControls page of the Component Palette, and drop down a `TDBGrid` object on `Form1`. In the Object Inspector, drop down the `DataSource` property of the `TDBGrid` object, and you will see the `TDataSource` object from the `DModBioLife` module listed. Select this item, and the grid will automatically fill up with data. If you drop down a `TDBEdit` control instead of a `TDBGrid` control, you proceed the same way, except that you will need to fill in not only the `DataSource` property in the Object Inspector, but also the `DataField` property. There is no need to type information into the `DataField` property, because it will automatically contain a list of the available fields in the `BioLife` table. The true significance of the Object Repository is only hinted at by this example. The importance of this tool is made more obvious if you have six or seven tables dropped onto a data module. You might then define several relationships between the tables and add other related code. For instance, you might have some one-to-many relationships established, possibly a many-to-many relationship established, and you might have several filters, lookups, and several calculated fields defined. Altogether, a data module of this type might encapsulate several sets of business rules defining exactly how tables should be accessed and how they relate to each other. The ability to save all this work in the repository, and to then automatically reuse it in multiple projects, is extremely valuable. I am, however, getting ahead of myself. Discussions of filters, lookups, calculated fields, and other database issues occur in various places over the next few chapters. ### The Database Explorer In addition to the data module, another key tool to use when working with databases is the Database Explorer. You can access the Database Explorer by choosing the Database | Explore menu item. The Explorer is a stand-alone executable, so you can also access it from the Windows Start button on the taskbar. You can use the Explorer even if BCB is not running, but BCB and the Explorer work best in conjunction with one another. Once you have loaded the Explorer, make sure you have selected the Databases page and not the Dictionary page. Click the `BCDEMOS` node to expose the `Tables` node. Now click the little plus sign before the `Tables` node. A list of all the tables in the database will appear. Select the `BioLife` table and choose the Data page to view the contents of the table, as shown in Fig- ure 8.8. FIGURE 8.8. Exploring the `BioLife` table in the Database Explorer. Click the little plus symbol in front of the `BioLife.db` node, and you will see a list of properties for the `BioLife` table. The properties listed are `Fields`, `Indices`, `Validity Checks`, `Referential Constraints`, `Security Specs`, and `Family Members`. You can expand each of these nodes to view their properties. For instance, if you select the `Fields` node, you will see a list of all the fields in the table. As you select each individual field, you will see a description of its primary characteristics. NOTE: The Database Explorer provides a means for viewing the text of stored procedures and triggers. You cannot edit these values, but you can view their code. The Database Explorer is a fairly complex tool with a number of powerful traits, many of which will undoubtedly be expanded in future versions of the product. In particular, you should note that it contains a `DataDictionary` that enables you to define a new alias or modify existing aliases. At this stage, I want to show you only one key trait of the Database Explorer. Arrange your screen so you can view both the Explorer and `Form1` at the same time. Select the `BioLife` table in the Explorer with the mouse, and then drag and drop it onto `Form1`. If you want, you can experiment further by expanding the `BioLife` node in the Explorer and dragging and dropping individual fields of the table onto `Form1`, just as you did when using the Fields Editor. If you start a new application, and then drag and drop the `BioLife` table onto `Form1` from the Explorer, you will find that the `TTable` and `TDataSource` objects are placed on `Form1`. If you want to move them off the form, you can add a `TDataModule` object to the project, and then select both the `TTable` and `TDataSource` objects and choose Edit | Cut from the menu. Now select the `TDataModule` object and choose Edit | Paste. Make sure `Form1` contains a reference (`#include`) to the unit that contains the `TDataModule`, and then hook up the grid to the `TDataSource` object in the `TDataModule`. This sounds like a fairly complicated process when written out, but you can perform this task in just a few seconds using BCB's visual tools. Once again, the last few paragraphs have done nothing more than introduce you to the Database Explorer. This is a complex tool that will prove useful to you in many different ways, some of which might not even have been apparent to its creator. For now, the key point to grasp is that it gives you an overview of all the data in a database and enables you to drag and drop fields and tables onto your forms. ### Working with the SQL Monitor The SQL Monitor is an advanced tool that enables you to see exactly what SQL statements are being generated by your application when you are running queries against SQL databases such as InterBase. The SQL Monitor only works when you are using an ODBC connection or SQL links to access real databases such as InterBase, Oracle, Sybase, or Informix. In other words, it is not useful when you are using the `TTable` object, or when you are accessing Paradox or dBASE tables. There is no trick to using the SQL Monitor. If it ships with your version of BCB, you can simply select the SQL monitor from the Database menu, run your program, and then browse through the SQL Monitor to see the specific statements generated by your program. Here is code produced from a simple SQL request for all the rows from the `Customer` table from the `IBLOCAL` alias. Note that in this case I am using an InterBase table. InterBase is an ANSI 92 SQL-compatible database server:
    1       18:49:40  SQL Prepare: INTRBASE - select * from Customer

    2       18:49:40  SQL Vendor: INTRBASE - isc_dsql_allocate_statement

    3       18:49:40  SQL Vendor: INTRBASE - isc_start_transaction

    4       18:49:40  SQL Vendor: INTRBASE - isc_dsql_prepare

    5       18:49:40  SQL Vendor: INTRBASE - isc_dsql_sql_info

    6       18:49:40  SQL Vendor: INTRBASE - isc_vax_integer

    7       18:49:40  SQL Transact: INTRBASE - XACT (UNKNOWN)

    8       18:49:40  SQL Vendor: INTRBASE - isc_commit_retaining

    9       18:49:40  SQL Execute: INTRBASE - select * from Customer

    10      18:49:40  SQL Vendor: INTRBASE - isc_dsql_execute

    11      18:49:40  SQL Stmt: INTRBASE - Fetch

    12      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    13      18:49:40  SQL Stmt: INTRBASE - Fetch

    14      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    15      18:49:40  SQL Stmt: INTRBASE - Fetch

    16      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    17      18:49:40  SQL Stmt: INTRBASE - Fetch

    18      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    19      18:49:40  SQL Stmt: INTRBASE - Fetch

    20      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    21      18:49:40  SQL Stmt: INTRBASE - Fetch

    22      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    23      18:49:40  SQL Stmt: INTRBASE - Fetch

    24      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    25      18:49:40  SQL Stmt: INTRBASE - Fetch

    26      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    27      18:49:40  SQL Stmt: INTRBASE - Fetch

    28      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    29      18:49:40  SQL Stmt: INTRBASE - Fetch

    30      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch

    31      18:49:40  SQL Stmt: INTRBASE - Fetch

    32      18:49:40  SQL Vendor: INTRBASE - isc_dsql_fetch
All this information can be a bit overwhelming at times. To simplify the output from the SQL Monitor, select Options | Trace Options from the SQL Monitor menu. The dialog shown in Figure 8.9 is launched. You can then select just the first two, or perhaps only the second option. The output from the same test run previously then looks like this:
    2       04:54:44  Log started for: Project1

    3       04:55:02  SQL Prepare: INTRBASE - select * from customer

    4       04:55:03  SQL Execute: INTRBASE - select * from customer
Now you see only the prepare and execute statements, which is probably all the information you needed. A screen shot of the SQL Monitor with this simple information in it is shown in Figure 8.10. Once again, the SQL Monitor is only for use with powerful databases such as InterBase or Oracle. I will present in-depth discussions of InterBase later in this section of the book. FIGURE 8.9. The SQL Monitor Trace Options dialog is accessible from one of the program's speed buttons. ###### FIGURE 8.10. The SQL Monitor showing prepared and executed SQL statements. ### ### Understanding the BDE and Aliases The BDE is the Borland Database Engine, which used to be called IDAPI. This engine is the gateway to all the databases accessed from BCB, except under certain unusual circumstances. The BDE gives you direct access to Paradox and dBASE tables. If you own a copy of Paradox or dBASE, you already have the BDE installed on your system. The brains behind these two products is the BDE. Paradox and dBASE are merely wrappers around the BDE, in much the same way that the BCB IDE is a wrapper around a C++ compiler. Besides giving you access to Paradox and dBASE, the BDE also uses Borland's SQL Links technology to give you fast access to client/server databases. In particular, BCB ships with SQL Links drivers for InterBase, Oracle, Sybase, MS SQL Server, DB2, and Informix. The list of available client/server databases changes depending on the version of the BDE you are using. To check the current list, open up the BDE Configuration application that ships with BCB and look at the available list of drivers. (See Figure 8.11.) FIGURE 8.11. The Borland Database Configuration utility (`BDECFG32.exe`) provides a list of available SQL Links drivers. The BDE Configuration utility lists the actual files that contain the SQL Links drivers. For instance, the SQL Links driver for Oracle is called `SQLORA32.dll`. The driver for InterBase is called `SQLINT32.dll`, and so on, as shown in Table 8.1. Table 8.1. DLLs associated with various SQL drivers.
Database | SQL Links driver names
DB2 | `SQLDB232.dll`
Informix | `SQLINF32.dll`
InterBase | `SQLINT32.dll`
MS SQL | `SQLMSS32.dll`
Oracle | `SQLORA32.dll`
Sybase | `SQLSSC32.dll`
The mere presence of the preceding DLLs is not enough, however, to connect you to a client/ server database. You will also need to be running a network protocol such as TCP/IP, and you will need local drivers and tools for the database supplied by the database vendor. The next section of this chapter discusses setting up TCP/IP on your computer. Each vendor will have a different set of local drivers and tools for you to use. For instance, if you want to connect to Oracle, you need to install the Oracle SQL Net tools. Your copy of BCB comes with a local version of InterBase. If you look at the right-hand portion of the taskbar at the bottom of your copy of Windows 95 or Windows NT, you should see the green and gray icon for the InterBase Server Properties applet. Other InterBase applets include the InterBase Server Manager (`IBMGR32.exe`) and InterBase Windows SQL (`WISQL32.exe`). (When reading these cryptic and inadvertently humorous 8/3 executable names, you should break the abbreviations up like this: `IB-MGR-32.exe`, not like this: `IBM-GR-32.exe`.) If you are running BCB, your connection to InterBase will be set up automatically during program install. However, if you want to connect to Oracle, Sybase, or some other SQL server, you can be in for a rather complicated ordeal. The difficult part of these installs is almost always setting up the third-party tools. Borland's half of the equation is usually automatic, and occurs without effort on your part during BCB's install. To get connected to Oracle or Sybase, the first thing to do is close BCB and all the Borland tools, and consult the manuals for your server. They will show you how to get the server set up, how to test the connection, and how to run some tools for managing your database. Once this part of the procedure is over, you can launch BCB, and you should be able to connect right away after establishing an alias. Aliases are described in the next section of this chapter, and in- depth in several of the upcoming chapters, including Chapter 15, "Working with the Local InterBase Server." They are also discussed in the readme file from the CD that accompanies this book. Of course, you have to have the client/server version of Delphi to connect to these databases. There is a Sams Publishing book called the Database Developer's Guide with Delphi 2 (Ken Henderson, ISBN 0-672-30862-2), which goes into considerable depth on connecting to most of the major servers. The portion of the book you want to see is Chapter 24, "Delphi's Database Drivers Demystified." Almost anything this book says about Delphi databases will apply equally to BCB's databases. Delphi and BCB are sister and brother tools, and their approach to databases is almost identical. I will, however, go to some lengths to ensure you are properly connected to InterBase, when I introduce that topic in Chapter 15. Until that time, there is no need for you to be connected to InterBase while reading this book. I should add, however, that connecting to InterBase is a simple task that should have been done for you automatically during the install of BCB. To check whether you are properly connected, try using the `IBLOCAL` alias set up by the install program for connecting to an InterBase table called `Employee.gdb`. ### Aliases You can create aliases inside any one of three tools: The SQL Explorer accessed through the Database | Explore menu option. From inside this tool, choose Object | New to create an alias. The Database Desktop. This is a stand-alone program installed by default in the `Program Files\Borland\Database Desktop` directory. Choose Tools | Alias Manager from the DBD menu to create an alias. The `BDECFG32.exe` program found in the directory where the BDE is installed. Turn to the Alias page in this program to create an alias. Both Paradox and InterBase aliases are created automatically when you install BCB. You can study these aliases as guides when creating your own aliases. Also see the readme files on the CD that accompanies this book, and the section on creating ODBC aliases near the end of this chapter. Various installation programs, such as Wise from Great Lakes Business Software (`www.glbs.com`, Tel (313) 981-4970, Fax (313) 981-9746) and InstallShield, can create aliases for you automatically. If you need to add aliases to a client machine during installation, you should let one of these programs handle it for you. They will also automate the installation of the BDE. InstallShield Express ships in the ISX directory found on some versions of the BCB CD. ### Some Notes on Installing TCP/IP In this section I briefly discuss the process of setting up TCP/IP on a Windows 95 machine. The process should be nearly identical on a Windows NT 4.0 machine, though the dialogs might have a slightly different name or appearance. TCP/IP is the protocol of choice when connecting to client/server databases. It ships automatically with the 32-bit Windows products. To see if it is installed on your system, open the Control Panel and launch the Network applet. If you have TCP/IP installed, it will show up on the Configuration page of this applet, as shown in Figure 8.12. FIGURE 8.12. The TCP/IP information from the Control Panel. If TCP is not installed, you should push the Add button on the Configuration page and bring up the Select Network Component Type dialog. Select Protocol from the list of drivers, and again choose the Add button. In the Select Network Protocol dialog, choose Microsoft in the left-hand list box, and TCP/IP in the right-hand list box. Windows will then install the necessary software, which may require the use of your Windows Install CD-ROM. You will probably also have to specify an IP address, subnet mask, gateway, and DNS server. This information can be garnered from your network administrator. If you are working on a small local network with Windows machines that you have set up in your office or home, you can ignore the DNS server, and can make up your own IP address, subnet mask, and gateway. For instance, the following numbers would do, as long as you are not connected to the real Internet, and are only talking to the machines in your home or office:
    IP Address: 143.186.186.2

    Subnet mask: 255.255.255.0

    Gateway: 143.186.186.1
The other machines on your network should have the same subnet and gateway, but the IP address should be unique. For instance, the next machine should have an IP address of `143.186.186.3`, and then `143.186.186.4`, and so on. Remember, don't make up your own numbers if you are connected to the real Internet! If you have an Internet connection, contact your network administrator or Internet service provider (ISP). NOTE: If you are appalled by my suggestion that people make up their own IP addresses, you should remember that many people connect their machines without being on the Internet. I do this all the time with two laptops when I am on the road showing BCB and Delphi. I also have a network at home with machines on it that are never connected directly to the Net. I want to stress, however, that if you are connected to the Internet, it is very simple to ask your IS department for an IP address for your computer. There are plenty of IP addresses in this world, and everyone can afford to have several for their own use. To check whether you are connected properly, open up a DOS window and try to ping one of the machines in your network. Ping is a built-in application that ships with Windows 95 and Windows NT. If you installed TCP/IP as explained previously, ping will be set up on your machine. To get started, you can try to ping yourself:
    Ping 143.186.186.2
Here is a built-in address for referencing your own machine:
    Ping 127.0.0.1
Or you can try to ping one of the other machines in your network:
    Ping 143.186.186.3
Here is the result of successful session:
    c:\4dos>ping 143.186.186.2

    Pinging 143.186.186.2 with 32 bytes of data:

    Reply from 143.186.186.2: bytes=32 time=55ms TTL=32

    Reply from 143.186.186.2: bytes=32 time=1ms TTL=32

    Reply from 143.186.186.2: bytes=32 time=1ms TTL=32

    Reply from 143.186.186.2: bytes=32 time=1ms TTL=32

    c:\4dos>
Here is the result of a failed session:
    c:\4dos>ping 143.186.186.3

    Pinging 143.186.186.3 with 32 bytes of data:

    Request timed out.

    Request timed out.

    Request timed out.

    Request timed out.

    c:\4dos>
Failed sessions usually occur because your machine is not configured properly or else the wires connecting you to the network are not set up correctly. (For instance, you might have forgotten to plug into the network!) If you are attached to the Internet and have a DNS server, you can try to ping one of the big servers on the Net:
    Ping compuserve.com
Here is successful session:
    c:\>ping compuserve.com

    Pinging compuserve.com [149.174.207.12] with 32 bytes of data:

    Reply from 149.174.207.12: bytes=32 time=298ms TTL=239

    Reply from 149.174.207.12: bytes=32 time=280ms TTL=239

    Reply from 149.174.207.12: bytes=32 time=333ms TTL=239

    Reply from 149.174.207.12: bytes=32 time=332ms TTL=239

    c:\>
Pinging `compuserve.com` is the same thing as pinging `149.174.207.12`. In fact, it's the job of the DNS server (the Domain Name Server) to resolve a human-readable name such as `compuserve.com` into an IP address. If you want to create a human-readable IP address on a local office or home network, you can edit the `HOSTS` files that ship with Windows 95 or Windows NT. Under Windows 95, you will find a sample `HOSTS` file called `Hosts.sam` in your Windows directory. Here is what this file looks like:
    # Copyright (c) 1994 Microsoft Corp.

    #

    # This is a sample HOSTS file used by Microsoft TCP/IP for Chicago

    #

    # This file contains the mappings of IP addresses to host names. Each

    # entry should be kept on an individual line. The IP address should

    # be placed in the first column followed by the corresponding host name.

    # The IP address and the host name should be separated by at least one

    # space.

    #

    # Additionally, comments (such as these) may be inserted on individual

    # lines or following the machine name denoted by a `#' symbol.

    #

    # For example:

    #

    #      102.54.94.97     rhino.acme.com          # source server

    #       38.25.63.10     x.acme.com              # x client host

    127.0.0.1 localhost
You can rename this file to `HOSTS.` with no extension, and then add your own list of IP address to it:
    143.186.186.3 MarysPC

    143.186.186.4 MikesPC
After doing this, you can ping the other machines with a human-readable name:
    ping maryspc
### Connecting to ODBC ODBC is a popular means of connecting to databases. For many developers, ODBC plays the same role in their development that the BDE plays in the life of Borland developers. ODBC is so popular that Borland has added a high-performance ODBC socket to the BDE that enables you to access data through ODBC. NOTE: It's important to understand that ODBC is not a database, but only a means of accessing a database. ODBC is a standard for creating drivers; it is a not a type of data. For instance, Borland developers commonly use SQL Links to connect to Oracle or Sybase. If you wanted, you could also use ODBC drivers in lieu of SQL Links, though this is usually not a wise thing to do because ODBC is often slow, while SQL Links are usually fast. The main appeal of ODBC is its popularity. There are ODBC drivers for connecting to nearly every database imaginable. If Borland does not ship with drivers for the data you want to access, the logical thing to do would be to search for ODBC drivers and then use them from BCB through the ODBC socket layer currently under discussion in this section of the chapter. Some copies of BCB ship with an ODBC driver for InterBase. Because this driver is readily available, I will use it as the model for this discussion. To get started using ODBC, you should close down your Borland tools, open up the Windows Control Panel, and start one of its applets called, quite poetically, "32 Bit ODBC." This applet can be used to manage the ODBC connections on your machine. As shown in Figure 8.13, on the bottom-right corner of the applet is a button with the word Driver on it, and another one right above it with the word Add on it. If you click either button you can see a list of the drivers available on your system. FIGURE 8.13. The 32-Bit ODBC application. On my system one of the available drivers is called InterBase 4.X driver by Visigenic (`*.gdb`). If I selected this button in the Add dialog, the dialog shown in Figure 8.14 would appear. FIGURE 8.14. The dialog used for configuring an ODBC connection. To set up a connection to the `Employee.gdb` table through the local InterBase server, I fill out the fields of the dialog, as shown in Figure 8.14. The database field of the dialog might have a path in it that looks like this:
    c:\program files\borland\intrbase\examples\employee.gdb
The rest of the fields should look like this:
    Data Source Name: InterBase Test

    Description: Test of InterBase

    Driver: Local

    DataBase: c:\program files\borland\intrbase\examples\employee.gdb

    User Name: SYSDBA

    Password: masterkey
You should enter `SYSDBA` as the user name, and enter `masterkey` as the password. After entering all this information, you should be able to connect to the table by pressing the Test Connect button. However, you may not be able to run this test until you first press OK in the dialog, and then bring the dialog back up by pressing the Settings button from the main screen of the 32-Bit ODBC applet. If all is working correctly, you can now bring up the BDE Configuration utility that ships with Borland C++Builder. On the drivers page pick New ODBC Connection to bring up the dialog for creating a new BDE connection, as shown in Figure 8.15. Fill in the fields as follows:
    SQL_Link_Driver:  ODBC_Test2

    Default ODBC Drive: InterBase 4.x Driver by Visigenic

    Default Data Source Name: InterBase Test.
After creating the driver for the ODBC test, switch to the Alias page of the BDE Configuration Utility. Select New Alias and type in `ODBCTest1`. Set the Alias type to `ODBC_Test2`. Click OK, save your work from the File menu, and exit `BDECFG.exe`. If BCB is already running, close it down. Now start up BCB and drop a `TTable` object on the main form. Select `ODBCTest1` from the list of available aliases in the `DatabaseName` property. Now proceed as you normally would, selecting a `TableName` and attaching a `TDataSource` and `TDBGrid` object to the table. When prompted for a password, enter `SYSDBA` as the Username and `masterkey` as the password. If you want, you can relaunch the BDE configuration program and set the user name for this alias permanently to `SYSDBA`. In the summary presented here, I have hardcoded in the names of the various drivers and aliases you create. You can, of course, name the alias anything you want, just as you can enter whatever names you want in the Data Source Name and Description fields of the ODBC Configuration dialog in the 32-Bit ODBC applet from the Control Panel. FIGURE 8.15. The dialog for creating a new ODBC. connection. ### ### Summary In this chapter you have learned some of the fundamental facts you need to know to start accessing databases from BCB. In particular, you have learned about the following: The basics of using `TTable` and `TQuery` The `TDataModule` object The SQL Monitor and Database Explorer TCP/IP and ODBC connectivity Now that the groundwork has been laid, the next few chapters start digging into the objects that BCB uses to access databases. As a rule, this is not a particularly difficult subject, but there is a good deal of information that needs to be covered. Once you have the facts that you need at your fingertips, you can start building real databases with BCB. C++Builder is one of the premier tools in the industry for accessing data, so you should be prepared to find yourself quickly writing powerful database applications that can be used by hundreds of people at one time.
| ©Copyright, Macmillan Computer Publishing. All rights reserved.